package com.example.www.demo4jdbctemplate.service.impl;

import com.example.www.demo4jdbctemplate.domain.User;
import com.example.www.demo4jdbctemplate.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;

/**
 * 作用说明：用户实现
 *
 * @author mirror.zhuyanpeng
 * @create 2017-08-26 1:22
 **/
@Service
public class UserServiceImpl implements UserService {
    @Autowired
    JdbcTemplate jdbcTemplate;

    /*DDL操作*/

    @Override
    public void creatUserTable() {
        jdbcTemplate.execute("CREATE TABLE USER_INFO (`id` int identity primary key not null ,name varchar(32) null ,`AGE` int not null)");
    }

    @Override
    public void dropUserTable() {
        jdbcTemplate.execute("DROP TABLE USER_INFO");
    }

    /*DML*/
    @Override
    public void insert(String name, Integer age) {
        jdbcTemplate.update("insert into USER_INFO(NAME,AGE) values(?,?)", name, age);
    }

    @Override
    public void insertBatch(List<User> users) {
        List<Object[]> params  = new ArrayList<>(users.size());
        users.forEach(user -> {
            params.add(new Object[]{user.getName(),user.getAge()});
        });
        jdbcTemplate.batchUpdate("insert into USER_INFO(NAME,AGE) values(?,?)",params);
    }

    @Override
    public void deleteById(Integer id) {
         jdbcTemplate.update("delete FROM USER_INFO where id= ?", id);
    }

    @Override
    public void deleteAllUsers() {
        jdbcTemplate.update("delete from USER_INFO ");
    }

    @Override
    public void updateById(User user){
        jdbcTemplate.update("UPDATE USER_INFO set NAME=?,AGE=? WHERE id = ?",user.getName(),user.getAge(),user.getId());
    }

    @Override
    public Integer count() {
        //查询返回自定义
        Integer count = jdbcTemplate.queryForObject("SELECT COUNT(1) FROM USER_INFO", Integer.class);
        return count;
    }

    @Override
    public User findById(Integer id) {
        BeanPropertyRowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
        User user = jdbcTemplate.queryForObject("SELECT id,name,age FROM USER_INFO WHERE id = ?", rowMapper, id);
        return user;
    }

    @Override
    public List<User> query() {
        BeanPropertyRowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
        List<User> query = jdbcTemplate.query("select id,name,age FROM USER_INFO", rowMapper);
        return query;
    }

    @Override
    public List<User> queryPage(Integer pageNumber, Integer pageSize) {
        BeanPropertyRowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
        List<User> query = jdbcTemplate.query("select id,name,age FROM USER_INFO LIMIT ?,?", rowMapper,pageNumber,pageSize);
        return query;
    }
}
